Week 11 of 16

Build: Migration Script

Write a one-time script to move your existing JSON data into SQLite.

Day 55 60 minutes Build

Day 55 of 80

What Is a Migration Script?

A migration script runs once to move data from one storage format to another. You have prompts in prompts.json. You want them in prompt_vault.db. You write a script that reads one and writes the other.

This is something professional developers do constantly — every time the data model changes, someone writes a migration. They're meant to be run once and kept around in case you need to re-run them.

Write migrate_to_sqlite.py

Create migrate_to_sqlite.py in your prompt-vault/ folder:

migrate_to_sqlite.py Python 
# migrate_to_sqlite.py — one-time migration from JSON to SQLite.
# Run with: python migrate_to_sqlite.py
# Safe to re-run — it checks before inserting.
import json
from pathlib import Path
from database import Database

JSON_FILE = "prompts.json"
DB_FILE = "prompt_vault.db"

print("=== Migrating prompts.json → SQLite ===\n")

# Step 1: Load the old JSON data
json_path = Path(JSON_FILE)
if not json_path.exists():
    print(f"No {JSON_FILE} found — nothing to migrate.")
    exit(0)

try:
    with open(json_path, "r") as f:
        prompts = json.load(f)
except json.JSONDecodeError as e:
    print(f"Error reading {JSON_FILE}: {e}")
    exit(1)

print(f"Found {len(prompts)} prompts in {JSON_FILE}")

if not prompts:
    print("Nothing to migrate.")
    exit(0)

# Step 2: Open the database and migrate
db = Database(DB_FILE)

# Check if the database already has data — warn before overwriting
existing = db.get_all()
if existing:
    print(f"\nWarning: {DB_FILE} already has {len(existing)} prompts.")
    confirm = input("Continue and add more? (y/n): ")
    if confirm.lower() != "y":
        print("Cancelled.")
        db.close()
        exit(0)

# Migrate each prompt
migrated = 0
errors = 0

for i, p in enumerate(prompts):
    try:
        # JSON structure: {"platform": "...", "shot": "...", "prompt": "..."}
        db.add_prompt(
            platform=p["platform"],
            shot=p["shot"],
            prompt_text=p["prompt"],
        )
        migrated += 1
        print(f"  [{i+1}/{len(prompts)}] Migrated: [{p['platform']}] {p['shot']}")
    except KeyError as e:
        print(f"  [{i+1}] Skipped (missing field {e}): {p}")
        errors += 1
    except Exception as e:
        print(f"  [{i+1}] Error: {e}")
        errors += 1

# Step 3: Summary
print(f"\n=== Migration Complete ===")
print(f"Migrated: {migrated} prompts")
if errors:
    print(f"Skipped:  {errors} prompts (check the errors above)")

print(f"\nBreakdown by platform:")
for platform, count in db.count_by_platform().items():
    print(f"  {platform}: {count}")

db.close()
print(f"\nDone. Your app is now using {DB_FILE}.")
print(f"Keep {JSON_FILE} as a backup — don't delete it yet.")

The duplicate check matters. If you run this script twice, you'd get duplicate entries. The check asks you before proceeding if data already exists. This is good defensive programming — always think about what happens if someone runs your script more than once.

Individual error handling per row means one bad entry won't stop the whole migration. The KeyError catch handles JSON entries with missing fields — if any of your old prompts are malformed, they get skipped with a message rather than crashing everything.

Don't delete the JSON file yet. Keep it as a backup until you've confirmed the SQLite version works correctly for a few days. Then you can archive it.

Run It

Before you run

Make sure your prompts.json is in the same directory as the script. Then run:

python migrate_to_sqlite.py

Open prompt_vault.db in a SQLite viewer (Cursor has one, or download DB Browser for SQLite). Browse your data — it's all there.

Week 11 Complete

You've moved from JSON files to a real database. Your Prompt Vault now uses SQLite — the same storage format used by millions of production applications (iOS apps, browsers, Android apps all use SQLite internally).

The code that talks to storage is isolated in database.py. If you ever switch to PostgreSQL, you only change that one file.

End of Week Checklist